#!/usr/bin/python3
import gc
import os

from openpyxl import load_workbook

from .Base_Plugin import Base_Plugin
from utils import String_Util


class Table_Column:
    def __init__(
        self,
        name: str,
        _type: str,
        is_not_null: False,
        unique: False,
        default_value,
        comment: str,
        sql_dialect: str,
    ) -> None:
        self.SPACE = " "
        self.name = String_Util.check_name(name)
        self._type = _type
        self.is_not_null = is_not_null
        self.unique = unique
        self.default_value = default_value
        self.comment = String_Util.check_comment_symbol(comment)
        self.is_pk = name == "REFCODE"
        self.is_autoincrement = False
        self.sql_dialect = sql_dialect

    def sql(self):
        sql = "{} {} {} {} {} {} {} {}".format(
            self.name,
            self._type,
            "NOT NULL" if self.is_not_null else self.SPACE,
            (
                (
                    "DEFAULT {}".format(self.default_value)
                    if "int" in self._type
                    or "bit" in self._type
                    or "decimal" in self._type
                    else 'DEFAULT "{}"'.format(self.default_value)
                )
                if self.default_value is not None and len(str(self.default_value)) > 0
                else self.SPACE
            ),
            (
                'COMMENT "{}"'.format(self.comment)
                if self.comment is not None and len(str(self.comment)) > 0
                else self.SPACE
            ),
            "PRIMARY KEY" if self.is_pk else self.SPACE,
            "AUTO_INCREMENT" if self.is_autoincrement else self.SPACE,
            "UNIQUE" if self.unique else self.SPACE,
        )
        sql = sql.strip()
        while "  " in sql:
            sql = sql.replace("  ", self.SPACE)
        return sql


class Table:

    def __init__(self, name: str, comment: str) -> None:
        self.name = String_Util.check_name(name)
        self.comment = String_Util.check_comment_symbol(comment)
        self.table_columns = list[Table_Column]()

    def add_columns(self, table_columns: list):
        self.table_columns.extend(table_columns)

    def sql(self, tenant, template=False):
        column_sql = list()
        for column in self.table_columns:
            column_sql.append(column.sql())

        if tenant is not None:
            table_name = "{}{}{}{}{}".format(
                tenant["table_prefix"],
                tenant["delimiter"],
                self.name,
                tenant["delimiter"] if template is False else "",
                tenant["tenant_id"] if template is False else "",
            )
        else:
            table_name = self.name

        return "CREATE TABLE {} (\r\t{}\r) {};\r\n".format(
            table_name,
            ",".join(column_sql).strip(),
            (
                'COMMENT "{}"'.format(self.comment)
                if len(self.comment) > 0
                else self.SPACE
            ),
        ).strip()


class Generate_SQL_Script(Base_Plugin):
    """生成SQL插件"""

    def __init__(self, context) -> None:
        super().__init__()
        self.context = context
        self.sql_dialect = self.context["sql_dialect"]

    def save_file(self, sql, dir):
        with open(dir, "w", encoding="utf-8") as f:
            f.write(sql)

    def get_columns(self, sheet):
        table_columns = list()
        for row in sheet.rows:
            if (
                row[0] is None
                or row[1].value is None
                or row[1].value == "列名"
                or not str(row[0].value).isdigit()
            ):
                continue
            table_columns.append(
                Table_Column(
                    name=row[1].value,
                    _type=row[2].value,
                    is_not_null=row[3].value,
                    unique=row[4].value,
                    default_value=row[5].value,
                    comment=row[6].value,
                    sql_dialect=self.sql_dialect,
                )
            )
        return table_columns

    def format(self, sql: str):
        return sql.replace(",", ",\r\t").replace(";", ";\r\n")

    def slim(self, sql: str):
        return sql.replace("\r\t", "").replace("\r\n", "").replace("\r", "")

    def run(self, workspace, source):
        workbook = load_workbook(os.path.join(workspace, source))
        _directory = workbook["目录"]

        table_sql = list()
        table_template_sql = list()
        table_tenant_sql = list()
        for row in _directory.rows:
            if row[0].value == "序号":
                continue
            if row[1].value is None:
                break

            table = Table(row[1].value, row[2].value)
            table.add_columns(self.get_columns(workbook[row[2].value]))
            table_sql.append(table.sql(None))

            table_template = Table(row[1].value, row[2].value)
            table_template.add_columns(self.get_columns(workbook[row[2].value]))
            table_template_sql.append(
                table.sql(self.context["init_tenant"], template=True)
            )

            table_tenant = Table(row[1].value, row[2].value)
            table_tenant.add_columns(self.get_columns(workbook[row[2].value]))
            table_tenant_sql.append(table.sql(self.context["init_tenant"]))

            self.processed += 1

        sql = "".join(table_sql)
        sql_template = "".join(table_template_sql)
        sql_tenant = "".join(table_tenant_sql)
        _target_path = os.path.join(workspace, "sql")
        if not os.path.exists(_target_path):
            os.mkdir(_target_path)
        self.save_file(self.slim(sql), os.path.join(_target_path, "schema-slim.sql"))
        self.save_file(self.format(sql), os.path.join(_target_path, "schema.sql"))
        self.save_file(
            self.format(sql_template), os.path.join(_target_path, "schema-template.sql")
        )
        self.save_file(
            self.format(sql_tenant), os.path.join(_target_path, "schema-tenant.sql")
        )
        del workbook
        gc.collect()
        return source
